package com.mlog.njyjy.ndop.share.sql.service;

import cn.hutool.core.io.IoUtil;
import cn.hutool.core.lang.Assert;
import cn.hutool.core.thread.ThreadUtil;
import cn.hutool.core.util.ReflectUtil;
import cn.hutool.core.util.StrUtil;
import com.jfinal.plugin.activerecord.*;
import com.jfinal.plugin.hikaricp.HikariCpPlugin;
import com.mlog.njyjy.ndop.share.sql.annotation.Join;
import com.mlog.njyjy.ndop.share.sql.annotation.Table;
import com.mlog.njyjy.ndop.share.sql.annotation.Transient;
import com.mlog.njyjy.ndop.share.sql.config.SqlDataSourceProperties;
import com.mlog.njyjy.ndop.share.sql.support.*;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.core.io.Resource;
import org.springframework.util.StringUtils;

import javax.sql.DataSource;
import java.io.IOException;
import java.lang.reflect.Field;
import java.sql.Connection;
import java.util.*;
import java.util.concurrent.ExecutorService;
import java.util.concurrent.LinkedBlockingQueue;
import java.util.concurrent.ThreadPoolExecutor;
import java.util.concurrent.TimeUnit;
import java.util.stream.Collectors;

/**
 * <p>
 * sql 环境初始化
 * - 根据model类生成数据库表
 * - 根据 schema.sql 生成数据库信息(只会执行一次)
 * </p>
 *
 * @author <a href="mailto:yingxiufeng@mlogcn.com">alex</a>
 * @version v1.0, 2020/7/8
 */
public class SqlPopulateService {

    private static final Logger log = LoggerFactory.getLogger(SqlPopulateService.class);

    private final SqlDataSourceProperties properties;

    private final HikariCpPlugin hikariCpPlugin;

    private ActiveRecordPlugin arp;

    /**
     * 数据库描述文件
     */
    private static final String SCHEMA_FILE = "schema.sql";

    /**
     * 初始化数据文件
     */
    private static final String DATA_FILE = "data.sql";

    private final ExecutorService workerPool = new ThreadPoolExecutor(32, 256,
            5L, TimeUnit.SECONDS, new LinkedBlockingQueue<>(512),
            ThreadUtil.newNamedThreadFactory("sql-data-thread", true), new ThreadPoolExecutor.AbortPolicy());


    public SqlPopulateService(SqlDataSourceProperties properties, HikariCpPlugin hikariCpPlugin) {
        this.properties = properties;
        this.hikariCpPlugin = hikariCpPlugin;
    }


    /**
     * initialize active record
     */
    public void init() {
        DataSource dataSource = hikariCpPlugin.getDataSource();
        Assert.notNull(dataSource, "dataSource cannot be null");

        SqlDataSourceProperties.SqlOrm orm = properties.getOrm();
        arp = new ActiveRecordPlugin(dataSource);
        arp.setDevMode(orm.isDevMode());
        arp.setShowSql(orm.isShowSql());

        Config dbConfig = arp.getConfig();
        DbKit.addConfig(dbConfig);

        boolean schemaUpdated = false;
        Resource schemaResource = ResourceLoaderUtil.loadResource(SCHEMA_FILE);
        if (schemaResource != null) {
            try {
                String sql = IoUtil.read(schemaResource.getInputStream(), "utf-8");
                Db.update(sql);
                log.info("[sql-orm] update `schema.sql` successfully.");
                schemaUpdated = true;
            } catch (IOException e) {
                log.error("[sql-orm] fetch `schema.sql` error: {}", e.getLocalizedMessage());
            }
        }
        Set<Class<?>> tblClasses = orm.getEntityPackages().stream()
                .map(pkg -> ClassScanUtil.scanPackageByAnnotation(pkg, Table.class, true))
                .flatMap(Collection::stream)
                .collect(Collectors.toCollection(HashSet::new));
        log.info("[sql-orm] find [{}] tables with ddl-auto: [{}]", tblClasses.size(), orm.getDdlAuto().name());

        tblClasses.forEach(cls -> {
            String tblName = SqlTableUtil.getTblName(cls);
            log.info("[sql-orm] processing table [{}] ", tblName);
            boolean existed = existOrNot(tblName);
            SqlTableColumn pkColumn = SqlTableUtil.findPkColumn(cls);
            if (pkColumn == null) {
                throw new SqlOrmException(StrUtil.format("primary key of [{}] must be specified", tblName));
            }
            switch (orm.getDdlAuto()) {
                case create_always:
                    if (existed) {
                        dropTable(tblName);
                    }
                    createTable(tblName, pkColumn, cls);
                    break;
                case create_if_not_exist:
                    if (existed) {
                        log.info("[sql-orm] table of [{}] already existed, will skip it", tblName);
                    } else {
                        createTable(tblName, pkColumn, cls);
                    }
                    break;
                case update:
                    // TODO:
                    break;
                case none:
                default:
                    break;
            }
            // 添加映射关系
            arp.addMapping(tblName, pkColumn.getField(), (Class<? extends Model<?>>) cls);
            log.info("[sql-orm] add table [{}] mapping of [{}] successfully", tblName, cls.getCanonicalName());
        });
        // 移除主配置 否则会有异常 TBD: main ?
        DbKit.removeConfig(DbKit.MAIN_CONFIG_NAME);

        // TODO: 添加sql 查询模板
///        arp.addSqlTemplate("");

        // 设置事务级别
        arp.setTransactionLevel(Connection.TRANSACTION_READ_COMMITTED);
        arp.start();

        // 读取 data.sql 并执行
        ThreadUtil.newThread(() -> {
            Resource resource = ResourceLoaderUtil.loadResource(DATA_FILE);
            if (resource != null) {
                try {
                    log.info("[sql-orm] generating data by `data.sql`");
                    String[] arr = IoUtil.read(resource.getInputStream(), "utf-8").split(";");
                    if (arr.length > 0) {
                        Arrays.stream(arr).filter(StringUtils::hasText)
                                .forEach(sql -> workerPool.submit(() -> Db.update(sql)));
                    }
                } catch (IOException e) {
                    log.error("[sql-orm] fetch `data.sql` error: {}", e.getLocalizedMessage());
                }
            } else {
                log.warn("[sql-orm] `data.sql` not found, skip this");
            }

        }, "sql-data-get-thread").start();

    }

    /**
     * detect table exist or not
     *
     * @param tblName
     * @return
     */
    private boolean existOrNot(String tblName) {
        List<Record> list = Db.find("show tables like '" + tblName + "'");
        return list.size() > 0;
    }

    /**
     * TODO
     * 根据model 类创建表
     * <strong>不支持复合主键</strong>
     *
     * @param tblClass
     * @param tblName
     * @return
     */
    private boolean createTable(String tblName, SqlTableColumn pkColumn, Class<?> tblClass) {
        Field[] fields;
        Class<?> superclass = tblClass.getSuperclass();
        if (superclass.isAssignableFrom(Model.class)) {
            fields = ReflectUtil.getFieldsDirectly(tblClass, false);
        } else {
            fields = ReflectUtil.getFieldsDirectly(tblClass, true);
        }

        List<SqlTableColumn> tableColumns = new LinkedList<SqlTableColumn>();
        // 过滤 {@link Transient} 标注的字段
        tableColumns = Arrays.stream(fields)
                .filter(field -> field.getAnnotation(Transient.class) == null && field.getAnnotation(Join.class) == null)
                .map(field -> SqlTableUtil.field2Column(field, tblClass))
                .filter(sqlTableColumn -> !pkColumn.getField().equals(sqlTableColumn.getField()))
                .collect(Collectors.toList());
//        for (int i = 0; i < fields.length; i++) {
//            Field field = fields[i];
//            SqlTableColumn tableColumn = SqlTableUtil.field2Column(field, tblClass);
//            if (pkColumn.getField().equals(tableColumn.getField())) {
//                continue;
//            }
//            tableColumns.add(tableColumn);
//        }
        // id column 加到首位
        tableColumns.add(0, pkColumn);

        Table table = tblClass.getAnnotation(Table.class);
        // 拼接 create table 语句
        String columnsClause = tableColumns.stream().map(SqlTableUtil::columnClause)
                .collect(Collectors.joining(",\r"));

        // 准备 unique key 以及 index 子句
        List<String> uniqueClauses = tableColumns.stream()
                .filter(SqlTableColumn::isUnique)
                .map(column -> StrUtil.format("UNIQUE KEY `uniq_{}` (`{}`)", column.getField(), column.getField()))
                .collect(Collectors.toList());
        Arrays.stream(table.uniques()).forEach(tableUnique -> {
            String keys = Arrays.stream(tableUnique.columns()).map(col -> "`" + StrUtil.toUnderlineCase(col) + "`").collect(Collectors.joining(","));
            String name = StrUtil.isNotBlank(tableUnique.name()) ? tableUnique.name() : Arrays.stream(tableUnique.columns()).map(StrUtil::toUnderlineCase).collect(Collectors.joining("_"));
            uniqueClauses.add(StrUtil.format("UNIQUE KEY `uniq_{}` ({})", name, keys));
        });

        String idxClause = Arrays.stream(table.indexes()).map(tableIndex -> {
            String idxName = tableIndex.name();
            if (StrUtil.isBlank(idxName)) {
                idxName = "idx_" + Arrays.stream(tableIndex.columns()).map(StrUtil::toUnderlineCase).collect(Collectors.joining("_"));
            }
            return StrUtil.format("KEY `{}` ({})", idxName, Arrays.stream(tableIndex.columns()).map(name -> "`" + StrUtil.toUnderlineCase(name) + "`").collect(Collectors.joining(",")));
        }).collect(Collectors.joining(","));

        // unique key ...
        String uniqueKeysClause = uniqueClauses.stream().collect(Collectors.joining(", \r"));

        String sql = StrUtil.format("CREATE TABLE IF NOT EXISTS `{}` ({}, PRIMARY KEY (`{}`) {} {}) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='{}'",
                tblName, columnsClause, pkColumn.getField(), StrUtil.isNotBlank(uniqueKeysClause) ? ",\r".concat(uniqueKeysClause) : "",
                StrUtil.isNotBlank(idxClause) ? ",\r".concat(idxClause) : "",
                table.comment());
        log.info("[sql-orm] sql: \r{} ", sql);
        try {
            // create table
            Db.update(sql);
        } catch (Exception e) {
            log.error("[sql-orm] create table error: {}", e.getLocalizedMessage());
        }
        log.info("[sql-orm] create table [{}] successfully", tblName);
        return true;
    }

    /**
     * drop table
     *
     * @param tblName
     * @return
     */
    private boolean dropTable(String tblName) {
        try {
            Db.update("DROP TABLE `?`", tblName);
        } catch (Exception e) {
            log.error(e.getLocalizedMessage());
            return false;
        }
        return true;
    }

    public void destroy() {
        if (arp != null) {
            arp.stop();
        }
    }

}
